﻿/*
 * GdoSqlCommand.prepareSpatialFilters
 * 
 * initial implementation : 20.9.2008
 *
 * Copyright (c) Adam Sida, asida@centrum.cz.
 * All rights reserved.
 * 
 * for license see Petra.Gis.License.txt
 */
using System;
using System.Text.RegularExpressions;
//using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Petra.Gis.DataServices;
using Petra.Gis.Geometry;
using Petra.Gis.Geometry.Support;
using Petra.Gis.DataServices.Metadata;


namespace Petra.Gis.DataServices.GDO.Sqlserver
{
  public partial class GdoSqlCommand : SpatialDbCommand
  {


    protected void prepareSpatialFilters()
    {

      // temorary condition, if no group by
      if (this.CommandText.ToLower().Contains("group by"))
        throw new ArgumentException("sorry, group by is currently unsupported");

      if (!this.Parameters.ContainsKey("Petra.Gis.DataServices.FeatureSpatialFilter"))
        return;

      //Dictionary<string, BoundingBox> spatialFilters = (Dictionary<string, BoundingBox>)this.Parameters["Petra.Gis.DataServices.FeatureSpatialFilter"].Value;
      FeatureSpatialFilter spatialFilters = (FeatureSpatialFilter)this.Parameters["Petra.Gis.DataServices.FeatureSpatialFilter"].Value;

      //todo:resolve unspecified field name
      //when feature has only one geom field no error
      //when has multiple throw error
      //if (spatialFilters.SpatialFieldName == "" || spatialFilters.SpatialFieldName == null)

      //if (Metadata == null)
      //  Metadata = getFeatureMetadata();

      if (Metadata == null)
        throw new ApplicationException(@"no metadata available for this command, 
                                         cant prepare spatial filters");

      // process spatial filter only if command type is text
      // command type tabledirect is not supported in sqlserver
      // if commandtype is procedure, then procedure must accept coarsespatialfilter as param
      // otherwise spatial filter is not applied
      if (this.CommandType == CommandType.Text)
      {
        // try get tablename from command.CommandText
        FeatureQualifier tq = parseTableQualifierFromSqlText(this.CommandText);
        if(tq.Alias != "")
          tq.Alias += ".";

        //DataTable t = _connection.GetSpatialSchema(SpatialSchemaTables.SpatialIndexes, new string[]{ tq.Catalog, tq.Schema, tq.Name, null, null});
        DataTable t = _connection.GetSpatialSchema(SpatialSchemaTables.SpatialIndexes, new string[] { tq.Catalog, tq.Schema, tq.Name, spatialFilters.SpatialFieldName, null });

        if(t.Rows.Count == 0)
          return; //no spatial filter available

        int i = 0;

        //for(int i = 0; i < t.Rows.Count; i++)
        //{
        if (t.Rows[i]["SPATIAL_INDEX_TYPE"].ToString().ToLower() == "none")
        {
          return; //no index for this table column
        }
        else if (t.Rows[i]["SPATIAL_INDEX_TYPE"].ToString().ToLower() == "boundsindex" &&
          spatialFilters.SpatialFieldName.ToLower() == t.Rows[i]["COLUMN_NAME"].ToString().ToLower())
        {
          prepareSpatialFilterByBounds(tq, t.Rows[i]["COLUMN_NAME"].ToString());
        }
        else if (t.Rows[i]["SPATIAL_INDEX_TYPE"].ToString().ToLower() == "tileindex" &&
          spatialFilters.SpatialFieldName.ToLower() == t.Rows[i]["COLUMN_NAME"].ToString().ToLower())
        {
          prepareTileSpatialFilterByBounds(tq, t.Rows[i]["COLUMN_NAME"].ToString());
        }
        else if (t.Rows[i]["SPATIAL_INDEX_TYPE"].ToString().ToLower() == "tileandboundsindex" &&
          spatialFilters.SpatialFieldName.ToLower() == t.Rows[i]["COLUMN_NAME"].ToString().ToLower())
        {
          prepareSpatialFilterByBounds(tq, t.Rows[i]["COLUMN_NAME"].ToString());
          prepareTileSpatialFilterByBounds(tq, t.Rows[i]["COLUMN_NAME"].ToString());
        }

        // prepare and build real sqlParameters...
        prepareSpatialFilterParameter("@CoarseSpatialFilter_xlo", spatialFilters.Bounds.Left);
        prepareSpatialFilterParameter("@CoarseSpatialFilter_ylo", spatialFilters.Bounds.Bottom);
        prepareSpatialFilterParameter("@CoarseSpatialFilter_xhi", spatialFilters.Bounds.Right);
        prepareSpatialFilterParameter("@CoarseSpatialFilter_yhi", spatialFilters.Bounds.Top);

          //break;
       
        //}

      }
    }





    protected void prepareSpatialFilterByBounds(FeatureQualifier tq, string geometryColumnName)
    {
      string tmp = this.CommandText;

      if (tmp.ToLower().Contains("where"))
        tmp += " and ";
      else
        tmp += " where ";

      tmp +=
          tq.Alias + geometryColumnName +  "_xlo < @CoarseSpatialFilter_xhi and " +
          tq.Alias + geometryColumnName + "_ylo < @CoarseSpatialFilter_yhi and " +
          tq.Alias + geometryColumnName + "_xhi > @CoarseSpatialFilter_xlo and " +
          tq.Alias + geometryColumnName + "_yhi > @CoarseSpatialFilter_ylo ";

      this.CommandText = tmp;
    }





    protected void prepareTileSpatialFilterByBounds(FeatureQualifier tq, string geometryColumnName)
    {
      // use local variable to
      // avoid refresh metadata when commandText change..
      string tmp = this.CommandText;

      // get tileIndex parameters from metadata..
      DataTable fd = _connection.GetSpatialSchema(SpatialSchemaTables.SpatialTileIndexesInfo, new string[]{tq.Catalog, tq.Schema, tq.Name, null, null, null, null, null });

      if( ((int)fd.Rows[0]["FILTER_OPTIONS"]) != 1)
        throw new ApplicationException("sorry FILTER_OPTIONS != 1 is unsupported now");

      if (tmp.ToLower().Contains("where"))
        tmp += " and ";
      else
        tmp += " where ";

      tmp +=
          tq.Alias + fd.Rows[0]["TILE_FIELD_NAME"].ToString() + " in ( select * from " +
          fd.Rows[0]["TILE_FN_NAME"].ToString() +
          "(@CoarseSpatialFilter_xlo, @CoarseSpatialFilter_ylo" +
          ", @CoarseSpatialFilter_xhi, @CoarseSpatialFilter_yhi) )";

      this.CommandText = tmp;
    }





    protected void prepareSpatialFilterParameter(string parameterName, double parameterValue)
    {
      if (_sqlCmd.Parameters.Contains(parameterName))
        _sqlCmd.Parameters[parameterName].Value = parameterValue;
      else
      {
        SqlParameter tmpParam = _sqlCmd.CreateParameter();
        tmpParam.ParameterName = parameterName;
        tmpParam.Value = parameterValue;
        _sqlCmd.Parameters.Add(tmpParam);
      }
    }
  }
}
